package others.swing.p006;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;

/**
 * 数据库连接
 */
public class DBconnect {
    Connection con;
    PreparedStatement sql1, sql2, sql3, sql4, sql5, sql6, sql7, sql8, sql9, sql10, sql11, sql12, sql13;
    ResultSet rs;

    public DBconnect() {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection("jdbc:odbc:dormitory", "", "");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    /*查找宿舍楼信息*/
    Vector select_Dor(int DorNo) {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Dormitory where DorNo= " + DorNo);
            while (rs.next()) {
                String s1 = rs.getString("DorNo");
                vc.add(s1);
                String s2 = rs.getString("DorPhNo");
                vc.add(s2);
                String s3 = rs.getString("DorAdminist");
                vc.add(s3);
                String s4 = rs.getString("DorSex");
                vc.add(s4);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*@@@@@@@@@@@@@@@查找全部宿舍楼信息*/
    Vector select_all_Dor() {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Dormitory  ");
            while (rs.next()) {
                String s1 = rs.getString("DorNo");
                vc.add(s1);
                String s2 = rs.getString("DorPhNo");
                vc.add(s2);
                String s3 = rs.getString("DorAdminist");
                vc.add(s3);
                String s4 = rs.getString("DorSex");
                vc.add(s4);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*查找楼栋管理员基本信息*/
    Vector select_Worker(String w) {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Worker where AdminName = \'" + w + "\'" + "or AdminNo = " + "'" + w + "'");
            while (rs.next()) {
                String s1 = rs.getString("AdminNo");
                vc.add(s1);
                String s2 = rs.getString("AdminName");
                vc.add(s2);
                String s3 = rs.getString("AdminType");
                vc.add(s3);
                String s4 = rs.getString("AdminWage");
                vc.add(s4);
                String s5 = rs.getString("AdminSex");
                vc.add(s5);
                String s6 = rs.getString("AdminPhNo");
                vc.add(s6);
                String s7 = rs.getString("AdminTime");
                vc.add(s7);
                String s8 = rs.getString("DorNo");
                vc.add(s8);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*@@@@@@@@@@查找全部楼栋管理员基本信息*/
    Vector select_all_Worker() {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Worker ");
            while (rs.next()) {
                String s1 = rs.getString("AdminNo");
                vc.add(s1);
                String s2 = rs.getString("AdminName");
                vc.add(s2);
                String s3 = rs.getString("AdminType");
                vc.add(s3);
                String s4 = rs.getString("AdminWage");
                vc.add(s4);
                String s5 = rs.getString("AdminSex");
                vc.add(s5);
                String s6 = rs.getString("AdminPhNo");
                vc.add(s6);
                String s7 = rs.getString("AdminTime");
                vc.add(s7);
                String s8 = rs.getString("DorNo");
                vc.add(s8);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /* 查找学生基本信息*/
    Vector select_Student(String s) {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Student where StuNo=" + "'" + s + "'" + "or StuName=" + "'" + s + "'");

            while (rs.next()) {
                String s1 = rs.getString("StuNo");
                vc.add(s1);
                String s2 = rs.getString("StuDep");
                vc.add(s2);
                String s3 = rs.getString("StuName");
                vc.add(s3);
                String s4 = rs.getString("StuSex");
                vc.add(s4);
                String s5 = rs.getString("StuHome");
                vc.add(s5);
                String s6 = rs.getString("StuBorth");
                vc.add(s6);
                String s7 = rs.getString("StuETime");
                vc.add(s7);
                String s8 = rs.getString("StuProfess");
                vc.add(s8);
                String s9 = rs.getString("StuClass");
                vc.add(s9);
                String s10 = rs.getString("RNo");
                vc.add(s10);
                String s11 = rs.getString("DorNo");
                vc.add(s11);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*@@@@@@@@@@@@@@@查找全部学生基本信息*/
    Vector select_all_Student() {
        Vector vc = new Vector();
        try {
            Statement st = this.con.createStatement();
            ResultSet rs = st.executeQuery("select * from Student");
            while (rs.next()) {
                String s1 = rs.getString("StuNo");
                vc.add(s1);
                String s2 = rs.getString("StuDep");
                vc.add(s2);
                String s3 = rs.getString("StuName");
                vc.add(s3);
                String s4 = rs.getString("StuSex");
                vc.add(s4);
                String s5 = rs.getString("StuHome");
                vc.add(s5);
                String s6 = rs.getString("StuBorth");
                vc.add(s6);
                String s7 = rs.getString("StuETime");
                vc.add(s7);
                String s8 = rs.getString("StuProfess");
                vc.add(s8);
                String s9 = rs.getString("StuClass");
                vc.add(s9);
                String s10 = rs.getString("RNo");
                vc.add(s10);
                String s11 = rs.getString("DorNo");
                vc.add(s11);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*查找寝室基本信息*/
    Vector select_Room(String r) {
        Vector vc = new Vector();
        try {
            Statement statement = this.con.createStatement();
            ResultSet rs = statement.executeQuery("select * from  Room where  RNo = " + "\'" + r + "\'" + "or ROne=" + "\'" + r + "\'" + "or RTwo=" + "\'" + r + "\'" + "or RThree=" + "\'" + r + "\'" + "or RFour=" + "\'" + r + "\'" + "or RFive=" + "\'" + r + "\'" + "or RSix=" + "\'" + r + "\'");
            while (rs.next()) {
                String Rno = rs.getString("RNo");
                vc.add(Rno);
                String one = rs.getString("ROne");
                vc.add(one);
                String two = rs.getString("RTwo");
                vc.add(two);
                String three = rs.getString("RThree");
                vc.add(three);
                String four = rs.getString("RFour");
                vc.add(four);
                String five = rs.getString("RFive");
                vc.add(five);
                String six = rs.getString("RSix");
                vc.add(six);
                String depart = rs.getString("RDepart");
                vc.add(depart);
                String dorNo = rs.getString("DorNo");
                vc.add(dorNo);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*@@@@@@@@@@@@@@@查找全部寝室基本信息*/
    Vector select_all_Room() {
        Vector vc = new Vector();
        try {
            Statement statement = this.con.createStatement();
            ResultSet rs = statement.executeQuery("select * from  Room ");
            while (rs.next()) {
                String Rno = rs.getString("RNo");
                vc.add(Rno);
                String one = rs.getString("ROne");
                vc.add(one);
                String two = rs.getString("RTwo");
                vc.add(two);
                String three = rs.getString("RThree");
                vc.add(three);
                String four = rs.getString("RFour");
                vc.add(four);
                String five = rs.getString("RFive");
                vc.add(five);
                String six = rs.getString("RSix");
                vc.add(six);
                String depart = rs.getString("RDepart");
                vc.add(depart);
                String dorNo = rs.getString("DorNo");
                vc.add(dorNo);
            }
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*插入楼栋管理员基本信息*/
    Vector insert_Worker(String adminno, String adminname, String admintype, String adminwage, String adminsex, String adminphno, String admintime, String dorno) {
        Vector vc = new Vector();
        try {
            sql7 = con.prepareStatement("insert into Worker values(?,?,?,?,?,?,?,?)");
            sql7.setString(1, adminno);
            sql7.setString(2, adminname);
            sql7.setString(3, admintype);
            sql7.setString(4, adminwage);
            sql7.setString(5, adminsex);
            sql7.setString(6, adminphno);
            sql7.setString(7, admintime);
            sql7.setString(8, dorno);
            sql7.executeUpdate();
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /* 插入学生基本信息*/
    Vector insert_Student(String stuno, String studep, String stuname, String stusex, String stuhome, String stuborth, String stuetime, String stuprofess, String stuclass, String rno, String dorno) {
        Vector vc = new Vector();
        try {
            sql8 = con.prepareStatement("insert into Student values(?,?,?,?,?,?,?,?,?,?,?)");
            sql8.setString(1, stuno);
            sql8.setString(2, studep);
            sql8.setString(3, stuname);
            sql8.setString(4, stusex);
            sql8.setString(5, stuhome);
            sql8.setString(6, stuborth);
            sql8.setString(7, stuetime);
            sql8.setString(8, stuprofess);
            sql8.setString(9, stuclass);
            sql8.setString(10, rno);
            sql8.setString(11, dorno);
            sql8.executeUpdate();
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*删除学生信息*/
    Vector delete_Student(String s) {
        Vector vc = new Vector();
        try {
            sql12 = con.prepareStatement("delete from Student where StuNo=" + "'" + s + "'");
            sql12.executeUpdate();
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    /*删除楼栋管理员信息*/
    Vector delete_Worker(String w) {
        Vector vc = new Vector();
        try {
            sql13 = con.prepareStatement("delete from Worker where AdminNo=" + "'" + w + "'");
            sql13.executeUpdate();
        } catch (SQLException e) {
            System.out.print(e);
        }
        return vc;
    }

    void closeDB() throws SQLException {
        try {
            con.close();
        } catch (SQLException e) {
        }
    }
}

/*#######查找宿舍楼信息界面*/
class table_d {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_d(int DorNo) {
        Vector data = sd.select_Dor(DorNo);
        int index = 0;
        String tabledata[][] = new String[data.size() / 4][4];
        for (int i = 0; i < data.size() / 4; i++) {
            for (int j = 0; j < 4; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"宿舍楼号", "电话", "楼管员", "宿舍性别"};
        JTable table = new JTable(tabledata, names);

        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("宿舍楼基本信息");
        f.pack();
        f.setVisible(true);
        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*#######查找全部宿舍楼信息界面*/
class table_all_d {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_all_d() {
        Vector data = sd.select_all_Dor();
        int index = 0;
        String tabledata[][] = new String[data.size() / 4][4];
        for (int i = 0; i < data.size() / 4; i++) {
            for (int j = 0; j < 4; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"宿舍楼号", "电话", "楼管员", "宿舍性别"};
        JTable table = new JTable(tabledata, names);
        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("宿舍楼基本信息");
        f.pack();
        f.setVisible(true);

        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*#######查找楼栋管理员基本信息界面*/
class table_w {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_w(String w) {
        Vector data = sd.select_Worker(w);
        int index = 0;
        String tabledata[][] = new String[data.size() / 8][8];
        for (int i = 0; i < data.size() / 8; i++) {
            for (int j = 0; j < 8; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"楼管编号", "楼管姓名", "工作类型", "月工资", "性别", "工作电话", "工作时间", "宿舍楼号"};
        JTable table = new JTable(tabledata, names);
        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("楼栋管理员基本信息");
        f.pack();
        f.setVisible(true);
        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*#######查找全部楼栋管理员基本信息界面*/
class table_all_w {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_all_w() {
        Vector data = sd.select_all_Worker();
        int index = 0;
        String tabledata[][] = new String[data.size() / 8][8];
        for (int i = 0; i < data.size() / 8; i++) {
            for (int j = 0; j < 8; j++) {
                tabledata[i][j] = (String) data.get(index++);

            }
        }
        String names[] = {"楼管编号", "楼管姓名", "工作类型", "月工资", "性别", "工作电话", "工作时间", "宿舍楼号"};
        JTable table = new JTable(tabledata, names);
        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("楼栋管理员基本信息");
        f.pack();
        f.setVisible(true);

        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*#######查找学生基本信息界面*/
class table_s {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_s(String s) {
        Vector data = sd.select_Student(s);
        int index = 0;
        String tabledata[][] = new String[data.size() / 11][11];
        for (int i = 0; i < data.size() / 11; i++) {
            for (int j = 0; j < 11; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"学生学号", "学院", "学生姓名", "性别", "籍贯", "出生日期", "入学日期", "专业", "班级", "寝室", "宿舍楼"};
        JTable table = new JTable(tabledata, names);
        table.setPreferredScrollableViewportSize(new Dimension(1000, 550));

        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("学生基本信息");
        f.pack();
        f.setVisible(true);

        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*######### 查找全部学生基本信息界面*/
class table_all_s {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_all_s() {
        Vector data = sd.select_all_Student();
        int index = 0;
        String tabledata[][] = new String[data.size() / 11][11];
        for (int i = 0; i < data.size() / 11; i++) {
            for (int j = 0; j < 11; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"学生学号", "学院", "学生姓名", "性别", "籍贯", "出生日期", "入学日期", "专业", "班级", "寝室", "宿舍楼"};
        JTable table = new JTable(tabledata, names);
        table.setPreferredScrollableViewportSize(new Dimension(1000, 550));

        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("学生基本信息");
        f.pack();
        f.setVisible(true);

        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*#######查找寝室基本信息界面*/
class table_r {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_r(String r) {

        Vector data = sd.select_Room(r);
        int index = 0;
        String tabledata[][] = new String[data.size() / 9][9];
        for (int i = 0; i < data.size() / 9; i++) {
            for (int j = 0; j < 9; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"寝室号", "舍员", "舍员", "舍员", "舍员", "舍员", "舍员", "学院", "宿舍楼"};

        JTable table = new JTable(tabledata, names);
        table.setPreferredScrollableViewportSize(new Dimension(800, 550));

        JScrollPane scrollpane = new JScrollPane(table);

        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("寝室基本信息");
        f.pack();
        f.setVisible(true);
        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}

/*## ##########查找全部寝室基本信息界面*/
class table_all_r {
    DBconnect sd = new DBconnect();
    JFrame f = new JFrame();

    public table_all_r() {
        Vector data = sd.select_all_Room();
        int index = 0;
        String tabledata[][] = new String[data.size() / 9][9];
        for (int i = 0; i < data.size() / 9; i++) {
            for (int j = 0; j < 9; j++) {
                tabledata[i][j] = (String) data.get(index++);
            }
        }
        String names[] = {"寝室号", "舍员", "舍员", "舍员", "舍员", "舍员", "舍员", "学院", "宿舍楼"};
        JTable table = new JTable(tabledata, names);
        table.setPreferredScrollableViewportSize(new Dimension(800, 550));
        JScrollPane scrollpane = new JScrollPane(table);
        f.getContentPane().add(scrollpane, BorderLayout.CENTER);
        f.setTitle("寝室基本信息");
        f.pack();
        f.setVisible(true);
        f.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                f.dispose();
            }
        });
    }
}





